'''
Author: kun 56216004@qq.com
Date: 2024-07-26 16:34:07
LastEditors: kun 56216004@qq.com
LastEditTime: 2024-08-09 10:59:39
FilePath: \langchain\21sql.py
Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE

LangChain 使用文本描述的方式操作MySQL中的数据_langchain mysql-CSDN博客
https://blog.csdn.net/qq_43692950/article/details/131378699
以及langChain官方文档 
'''

import os
# from langchain import OpenAI, SQLDatabase, SQLDatabaseChain
from langchain import  SQLDatabase, SQLDatabaseChain
from langchain.llms import OpenAI
import openai
import os
from config import * 
openai.api_base = "https://api.chatanywhere.com.cn/v1"
os.environ["OPENAI_API_KEY"] = api_key # 填入OpenAI Secret Key
llm = OpenAI(model_name="gpt-4o-mini")   #text-davinci-003 模型不存在 gpt-4o-mini
# llm = OpenAI(temperature=0, openai_api_key=api_key)



# db_user = "p_english"
# db_password = "p_en****1"
# db_host = "12*.*.**3.5,3306"
# db_name = "p_english"
db = SQLDatabase.from_uri(f"mysql+pymysql://{db_user}:{db_password}@{db_host}/{db_name}")


db_chain = SQLDatabaseChain(llm=llm, database=db, verbose=True)

def get_res(questions):
    res = db_chain.run(questions)
    print("问题：", questions, "解答：", res)
    return res

if __name__ == '__main__':

    questions = "合同表里有多少条数据?"
    res = db_chain.run(questions)
    print("问题：", questions, "解答：", res)

    questions = "单词表里有多少个单词?"
    # res = db_chain.run(questions)
    # print("问题：", questions, "解答：", res)

    # SQLQuery: SELECT COUNT(`ID`) AS `TotalWords` FROM `word`

    questions = "每个字母组合发音最多的音标是什么?"
    res = db_chain.run(questions)
    print("问题：", questions, "解答：", res)

    """  SQLQuery: 
    ```sql
    SELECT `letter`, `pronunce`, COUNT(`pronunce`) AS `pronunce_count`
    FROM pronuncerule
    GROUP BY `letter`, `pronunce`
    ORDER BY `pronunce_count` DESC
    LIMIT 5;
    ```
    """